import connector.driver
from connector.driver import DatabaseDriver


# 管理员界面与用户界面通用操作

# 通过起始站、终点站和日期检索车票信息
# 完成
def get_ticket_by_starting_terminal(driver: DatabaseDriver, date: str, starting_station: str = "",
                                    terminal_station: str = "") -> tuple:
    sql = ""
    if starting_station == "" and terminal_station == "":
        # tuple：元组，根据日期从ticket表中检索所有车票信息
        results: tuple = driver.query_all("select * from ticket where date = '{}'".format(date))
        return results
    else:
        if starting_station != "" and terminal_station != "":
            # 根据日期、起始站和终点站从ticket表中检索信息
            sql: str = (
                "select * from ticket where date = '{}' and Starting_station like '%{}%' and Terminal_station like '%{}%'"
                .format(date, starting_station, terminal_station))
            # sql=sql：将 SQL 查询字符串作为参数传递给 driver.query_all 方法
            # log=True：执行查询的同时会记录日志
            return driver.query_all(sql=sql, log=True)
        elif starting_station is not None:
            sql: str = ("select * from ticket where date = '{}' and Starting_station like '%{}%'"
                        .format(date, starting_station))
            return driver.query_all(sql=sql, log=True)
        elif terminal_station is not None:
            sql: str = ("select * from ticket where date = '{}' and Terminal_station like '%{}%'"
                        .format(date, terminal_station))
            return driver.query_all(sql=sql, log=True)


# 管理员界面专属操作

# 添加新的车票信息
def insert_ticket(driver: DatabaseDriver, train_number: str, date: str,
                  starting_station: str, terminal_station: str,
                  departure_time: str, arrival_time: str) -> tuple:
    return driver.insert("insert into ticket"
                         "(train_number,date,"
                         "starting_station,terminal_station,"
                         "departure_time,arrival_time)"
                         "values ('{}','{}','{}','{}','{}','{}')".format(train_number, date, starting_station,
                                                                         terminal_station, departure_time,
                                                                         arrival_time))


# 删除车票信息
def delete_ticket(driver: DatabaseDriver, train_id: str):
    return driver.delete("delete from ticket where train_id = '{}'"
                         .format(train_id))


# 查询车次id
def get_train_id(driver: DatabaseDriver, Train_id: str) -> tuple:
    return driver.query_one("select * from ticket where Train_id = '{}'"
                            .format(Train_id))
